Create a table in PostgreSQL using Python and Psycopg

Overview:

A database table in PostgreSQL is created using the SQL Statement CREATE TABLE.
CREATE TABLE is a one of the essential DDL statements supported by PostgreSQL.
A PostgreSQL user, can create a new database in PostgreSQL server and add tables into it or can create tables in an already existing PostgreSQL database.
Inside a database, tables are stored under a schema. When a schema name is not specified tables are created inside a default schema named public.
To create a table from a Python Program, Psycopg - the Python client library for PostgreSQL can be used.

Creating a database table in PostgreSQL using Python and Psycopg:

The implementation of Psycopg adheres to the Python Database Access Specification.
In a Python Program, using the Psycopg, a developer can create a database connection, a database cursor and call the necessary execute() and fetch() methods to perform any of the required SQL operations on a PostgreSQL server.
The CREATE TABLE statement is as well, executed through a database cursor object obtained using the Psycopg module.
The Python example program below, creates a table in PostgreSQL server and prints the list of tables under the default schema, in this case a schema named test,not the standard default schema.

The search_path in PostgreSQL:

When a table is queried for data the table name is resolved by PostgreSQL using the search path. The default search path is "$user", public.
The first entry in the default search path is the username. If a schema exists with the same username, then the table is queried in that schema and the query process stops. Else it looks for the table in the public schema of the database.
The search path can be altered to have different sequence using the SET command in the PSQL terminal or using the ALTER statement.

Eg., SET search_path TO custom, public, "$user"
ALTER DATABASE test SET search_path TO custom, public, "$user"
Both the SET command and the ALTER statement change the search order of the schemas.

Example:

# Example Python program that creates a PostgreSQL table
# underneath a schema which is named the same
# as the current user

# import the PostgreSQL adapter for Python
import psycopg

# Connect to the PostgreSQL database server
postgresConnection = psycopg.connect("dbname=test user=test password='test'")

# Get cursor object from the database connection
cursor = postgresConnection.cursor()
name_Table = "test.news_stories"

# Create table statement
sqlCreateTable = "create table "+name_Table+" (id bigint, title varchar(128), summary varchar(256), story text);"

# Create a table in PostgreSQL database
cursor.execute(sqlCreateTable)
postgresConnection.commit()

# Get the updated list of tables
sqlGetTableList = "SELECT table_name FROM information_schema.tables where table_schema='test';"

# Retrieve all the rows from the cursor
cursor.execute(sqlGetTableList)
tables = cursor.fetchall()

# Print the names of the tables
for table in tables:
print(table)

Output:

List of tables under current schema
('news_stories',)